from xml.dom.minidom import parse, parseString, Node
import re
idCounters = {}
def isLeaf(node):
    return not [1 for n in node.childNodes if n.nodeType == Node.ELEMENT_NODE]

def genID(nodeName):
    global idCounters
    if nodeName in idCounters:
        idCounters[nodeName] = idCounters[nodeName] + 1
    else:
        idCounters[nodeName] = 1
    return idCounters[nodeName] 

def getCurrentID(nodeName):
    global idCounters
    if nodeName in idCounters:
        return idCounters[nodeName]
    else:
        idCounters[nodeName] = 1
    return idCounters[nodeName] 

def getType(value):
    #see if it's an integer
    integere = re.compile('\d+')
    res = integere.match(value)
    if res != None and len(res.group()) == len(value):
        return 'INTEGER'
    #see if it's a double
    doublee = re.compile('\d+[.]\d+|\d+e\d+')
    res = doublee.match(value)
    if res != None and len(res.group()) == len(value):
        return 'DOUBLE PRECISION'
    #None of the above? Must be a string
    return 'VARCHAR(255)'
    
def handleNode(node, elementList, prevIds):
    values = []
    createTableText = ''
    insertText = ''
    childCreateTableText = ''
    childInsertText = ''
    nextIds = {}
    if not isLeaf(node):
        if not node.nodeName in elementList:
            if node.nodeType == Node.ELEMENT_NODE:
                createTableText += 'CREATE TABLE ' + node.nodeName \
                + '(\n\t' + node.nodeName + '_id INTEGER PRIMARY KEY,\n\t'
                for n in node.childNodes:
                    if n.nodeType == Node.ELEMENT_NODE:
                        name = n.nodeName                     
                        if isLeaf(n):
                            type = getType(n.firstChild.nodeValue)
                            createTableText += name + ' '+ type +',\n\t'
                        else:
                            nextIds[node.nodeName] = -1
                type = 'INTEGER'
                for key in prevIds.keys():
                    createTableText += key + '_id '+ type + ',\n\t'
                createTableText = createTableText.rstrip(',\n\t')
                createTableText += ');\n'
            elementList.append(node.nodeName)
        insertText += 'INSERT INTO '+node.nodeName+'('+node.nodeName+'_id,'
        for n in node.childNodes:
            if n.nodeType == Node.ELEMENT_NODE:               
                if isLeaf(n):
                    insertText += n.nodeName
                    value = n.firstChild.nodeValue
                    type = getType(value)
                    insertText += ','
                    if type.find('VARCHAR') != -1 or type.find('TEXT') != -1:
                        values.append('\''+value+'\'')
                    else:
                        values.append(value)
        for key in prevIds.keys():
            insertText += key + '_id,'
            values.append(prevIds[key])
        insertText = insertText.rstrip(',')  
        insertText += ') VALUES ('
        currId = genID(node.nodeName)  
        nextIds[node.nodeName] = currId   
        insertText += str(currId) + ','                                    
        for v in values:
            if v != None:
                insertText += str(v) + ','
        insertText = insertText.rstrip(',')
        insertText += ');\n'
        for n in node.childNodes: 
           childCreateTableText, childInsertText = handleNode(n, elementList, nextIds)
           createTableText += childCreateTableText
           insertText += childInsertText
    return createTableText, insertText

                  
def convert(filename):
    insertText = ''
    createTableText = ''
    childCreateTableText = ''
    childInsertText = ''
    elementList = []
    global idCounters
    idCounters = {}
    document = parse(filename)
    root = document.firstChild
    if root.nodeType == Node.DOCUMENT_TYPE_NODE:
        root = root.nextSibling
    createDatabaseText = 'CREATE DATABASE '+root.nodeName + ';\n'
    for node in root.childNodes:
      childCreateTableText, childInsertText = handleNode(node, elementList, {})
      createTableText += childCreateTableText
      insertText += childInsertText
    return createDatabaseText+createTableText+insertText      
     
 
        


